package model.admin;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import myutil.Connect;

import vo.BatchDetailsVo;
import vo.mixed.CourseListVo;
import vo.mixed.ProfessorListVo;


public class GetCourseListMo {
	
	public ArrayList<ProfessorListVo> getCourseProfessorDetails(int course_id,int department_id){
		ArrayList<ProfessorListVo> course_prof_list = new ArrayList<ProfessorListVo>();
		Connect c=new Connect();
		String query="";
		if(department_id==0){
		query="select s.staff_name,s.staff_id from course_details c,course_professor p,college_staff s where c.course_id=p.course_id  and(s.staff_id =p.professor_id_1 or s.staff_id =p.professor_id_2 or s.staff_id =p.professor_id_3  or s.staff_id=p.professor_id_4)  and p.course_id="+course_id+"";
		}else if(course_id==0){
			query="select s.staff_name,s.staff_id from professor_department pd, college_staff s where pd.status='Active' and pd.department_id="+department_id+" and pd.professor_id=s.staff_id";
		}
		try {
			
		ResultSet rs=c.getResult(query);

			while(rs.next()){
		ProfessorListVo single_object = new ProfessorListVo();
		single_object.setStaff_id(rs.getInt("s.staff_id"));
		single_object.setStaff_name(rs.getString("s.staff_name"));
		single_object.setDepartment_id(department_id);
		course_prof_list.add(single_object);
		System.out.println("Professor details"+single_object.getStaff_id()+" and name is "+single_object.getStaff_name());	
		
		}
	} catch (Exception e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	}
		
		return course_prof_list;
	}
	
	public void deleteDepartmentProfessor(int dep_id,int prof_id,String type){
		String query="";
		Connect c= new Connect();
		query="update professor_department set status='Inactive' where department_id="+dep_id+" and professor_id="+prof_id+"";
		System.out.println(query);
		try {
			c.dml(query);
			c.logger(type, "Delete");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
public ArrayList<ProfessorListVo> getDepartmentProfeesor(int department_id){
	ArrayList< ProfessorListVo> prof = new ArrayList<ProfessorListVo>();
	String query="";
	Connect c= new Connect();
	query="SELECT staff_id, staff_name FROM college_staff WHERE staff_id NOT IN (SELECT professor_id FROM professor_department WHERE department_id ="+department_id+")";
	System.out.println("\n "+query);
	try {
		ResultSet rs= c.getResult(query);
		while(rs.next()){
			ProfessorListVo vo = new ProfessorListVo();
			vo.setStaff_id(rs.getInt("staff_id"));
			vo.setStaff_name(rs.getString("staff_name"));
			vo.setDepartment_id(department_id);
			prof.add(vo);
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return prof;
}

public ArrayList<CourseListVo> getCourseList(int professor_id)
{
	ArrayList<CourseListVo> course_list= new ArrayList<CourseListVo>();
	String query="";
	if (professor_id==0)
		 query="select * from course_details where status='Active'";
	else 
		query="select * " +
				"from " +
				"course_details" +
			  " where course_id in (select course_id " +
			  						 "from course_professor " +
			  						"where professor_id_1	=	"+professor_id+" " +
			  						   "or professor_id_2	=	"+professor_id+" " +
			  						   "or professor_id_3  =   "+professor_id+" " +
			  						   "or professor_id_4	=	"+professor_id+");";
	Connect c=new Connect();
	try {
	ResultSet rs=c.getResult(query);
System.out.println("\n "+query);
		while(rs.next()){
	
		CourseListVo single_object=new CourseListVo();
		single_object.setCourse_code(rs.getString("course_code"));
		single_object.setCourse_id(rs.getInt("course_id"));
		single_object.setCourse_name(rs.getString("course_name"));
		single_object.setCredit(rs.getInt("credit"));
		single_object.setDepartment_id(rs.getInt("department_id"));
		single_object.setLab(rs.getInt("lab"));
		single_object.setTheory(rs.getInt("theory"));

		course_list.add(single_object);
		System.out.println("siva "+single_object.getCourse_name());
		
	}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
	return course_list;
}
public  ArrayList<CourseListVo> getDegreeDepartmentCourseList(int degree_id, int department_id) throws Exception{
	ArrayList<CourseListVo> batch_department_list = new ArrayList<CourseListVo>(); 
	Connect c=new Connect();
	
	String query="";
	if (department_id==0)
		query="SELECT DISTINCT cd.course_name, cd.course_code, cd.course_id FROM degree_course dc, course_details cd WHERE dc.degree_id ="+degree_id+" AND dc.course_id = cd.course_id";
	
	else 
	query="select c.course_name," +
    	" c.course_code," +
    	" c.course_id  " +
   " from degree_department d, " +
   		" course_details c, " +
   		" department_details dd " +
   "where d.department_id	=	dd.department_id " +
   	 "and c.department_id	=	d.department_id " +
   	 "and d.department_id= " +department_id+" " +
   	 "and d.degree_id="+degree_id+";";
   	 
   	 System.out.println(query+"\n");
	ResultSet rs= null;
	try {
		rs = c.getResult(query);		
		while(rs.next()){
			CourseListVo single_object = new CourseListVo();
			single_object.setCourse_code(rs.getString("course_code"));
			single_object.setCourse_id(rs.getInt("course_id"));
//			single_object.setDepartment_id(rs.getInt("dd.department_id"));
//			single_object.setDepartment_name(rs.getString("dd.department_name"));
			single_object.setCourse_name(rs.getString("course_name"));
			
			batch_department_list.add(single_object);
			
			
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return batch_department_list;
}

public void updateDegreeDep(int degree_id,int department_id,String value){
	String query="";
	Connect c= new Connect();
	query="insert into degree_department_mgmt(department_id,degree_id,description,status) values("+ department_id+" , "+degree_id +" ,(select degree_name from degree_details where degree_id="+degree_id+") ,'Active')";
	System.out.println(query);
	try {
		
		c.dml(query);
		c.logger(value, "Insert");
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}

public void deleteDegreeDepartment(int degree_id,int department_id,String type){
	String query="";
	Connect c= new Connect();
	
	query="update degree_department_mgmt set status='Inactive' where degree_id="+degree_id+" and department_id="+department_id+"";
	System.out.println(query);
	try {
		c.dml(query);
		c.logger(type, "Delete");
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}

}
